"""
数据导入服务
"""
import pandas as pd
import numpy as np
from datetime import datetime
from app.models.user import User, StudentProfile, TeacherProfile
from app.models.course import Course, CourseTag
from app.models.student_course import StudentCourse
from app.extensions import db


class DataImporter:
    """数据导入器"""
    
    def __init__(self):
        self.errors = []
        self.success_count = 0
        self.fail_count = 0
        
        # 中文字段映射表
        self.field_mapping = {
            # 学生字段
            '学号': 'student_id',
            '邮箱': 'email',
            '密码': 'password',
            '名': 'first_name',
            '姓': 'last_name',
            '手机号': 'phone',
            '学院': 'college',
            '系部': 'department',
            '专业': 'major',
            '年级': 'grade_level',
            '入学日期': 'enrollment_date',
            '用户名': 'username',
            
            # 教师字段
            '工号': 'employee_id',
            '职称': 'title',
            
            # 课程字段
            '课程代码': 'course_code',  # 用于成绩导入
            '课程名称': 'name',
            '课程描述': 'description',
            '学分': 'credits',
            '院系': 'department',
            '授课教师ID': 'instructor_id',
            '学期(旧)': 'semester',
            '学年': 'academic_year',
            '学期': 'semester_term',
            '课程类别': 'course_category',
            '教学班号': 'class_number',
            '最大人数': 'max_students',
            '标签': 'tags',
            
            # 课程字段别名（用于课程导入）
            'code': 'code',  # 保持英文字段名兼容
            
            # 成绩字段
            '选课日期': 'course_enrollment_date',  # 避免与入学日期重复
            '考试类型': 'exam_type',
            '学分类型': 'credit_type',
            '绩点': 'gpa',
            '状态': 'status',
            '总评成绩': 'score',
            '平时成绩': 'midterm_score',
            '期中成绩': 'midterm_score',  # 兼容旧格式
            '考试成绩': 'final_score',
            '期末成绩': 'final_score',  # 兼容旧格式
            '总课时': 'total_classes',
            '出勤课时': 'attended',
            '出勤率': 'attendance_rate'
        }
    
    def map_chinese_columns(self, df):
        """将中文列名映射为英文列名"""
        # 创建新的列名映射
        new_columns = {}
        for col in df.columns:
            # 如果列名在映射表中，使用映射后的名称
            if col in self.field_mapping:
                new_columns[col] = self.field_mapping[col]
            else:
                # 否则保持原名
                new_columns[col] = col
        
        # 重命名列
        df = df.rename(columns=new_columns)
        
        # 特殊处理：如果有course_code但没有code，复制一份
        if 'course_code' in df.columns and 'code' not in df.columns:
            df['code'] = df['course_code']
        
        return df
    
    def read_file(self, file_path):
        """读取文件，自动尝试多种编码格式"""
        if file_path.endswith('.csv'):
            # 尝试多种编码格式读取CSV
            encodings = ['utf-8', 'gbk', 'gb2312', 'gb18030', 'latin1']
            for encoding in encodings:
                try:
                    df = pd.read_csv(file_path, encoding=encoding)
                    # 映射中文列名
                    df = self.map_chinese_columns(df)
                    return df
                except (UnicodeDecodeError, UnicodeError):
                    continue
                except Exception as e:
                    # 其他错误（如文件不存在等）直接抛出
                    raise e
            # 所有编码都失败
            raise ValueError("无法识别CSV文件编码格式，请确保文件为UTF-8、GBK或GB2312编码")
        else:
            # Excel文件读取；若失败，尝试按CSV回退一次（应对扩展名异常）
            try:
                df = pd.read_excel(file_path)
                df = self.map_chinese_columns(df)
                return df
            except Exception:
                # 回退尝试CSV（常见于扩展名缺失或错误导致pandas误判）
                try:
                    df = pd.read_csv(file_path, encoding='utf-8')
                    df = self.map_chinese_columns(df)
                    return df
                except Exception as e:
                    raise e
    
    def clean_data(self, df):
        """数据清洗"""
        # 处理缺失值
        df = df.fillna({
            'score': 0,
            'midterm_score': 0,
            'final_score': 0,
            'attendance_rate': 0
        })
        
        # 去除重复记录
        df = df.drop_duplicates()
        
        # 数据类型转换
        numeric_columns = ['score', 'midterm_score', 'final_score', 'attendance_rate']
        for col in numeric_columns:
            if col in df.columns:
                df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)
                # 限制分数范围
                if 'score' in col:
                    df[col] = df[col].clip(0, 100)
                elif 'rate' in col:
                    df[col] = df[col].clip(0, 100)
        
        return df
    
    def import_teachers(self, file_path):
        """导入教师数据"""
        try:
            # 读取文件
            df = self.read_file(file_path)
            
            # 清洗数据
            df = self.clean_data(df)
            
            # 必需字段验证
            required_columns = ['employee_id', 'email']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                self.errors.append(f"Missing required columns: {', '.join(missing_columns)}")
                return False
            
            # 逐行导入
            for idx, row in df.iterrows():
                try:
                    # 使用工号作为username（忽略CSV中可能存在的username列）
                    username = str(row['employee_id']).strip()
                    
                    # 检查工号是否已存在
                    existing_user = User.query.filter_by(username=username).first()
                    if existing_user:
                        self.errors.append(f"Row {idx+2}: Employee ID '{username}' already exists")
                        self.fail_count += 1
                        continue
                    
                    # 创建用户（使用工号作为username）
                    user = User(
                        username=username,  # 使用工号作为username
                        email=str(row['email']).strip() if pd.notna(row.get('email')) else '',
                        role='teacher',
                        first_name=str(row.get('first_name', '')).strip() if pd.notna(row.get('first_name')) else '',
                        last_name=str(row.get('last_name', '')).strip() if pd.notna(row.get('last_name')) else '',
                        phone=str(row.get('phone', '')).strip() if pd.notna(row.get('phone')) else '',
                        college=str(row.get('college', '')).strip() if pd.notna(row.get('college')) else '',
                        department=str(row.get('department', '')).strip() if pd.notna(row.get('department')) else ''
                    )
                    user.set_password(row.get('password', 'password123'))
                    
                    db.session.add(user)
                    db.session.flush()  # 获取user.id
                    
                    # 创建教师profile
                    profile = TeacherProfile(
                        user_id=user.id,
                        employee_id=row['employee_id'],
                        department=row.get('department', ''),
                        title=row.get('title', '')
                    )
                    
                    db.session.add(profile)
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"Row {idx+2}: {str(e)}")
                    self.fail_count += 1
                    db.session.rollback()
                    continue
            
            db.session.commit()
            return True
            
        except Exception as e:
            self.errors.append(f"File reading error: {str(e)}")
            db.session.rollback()
            return False
    
    def import_students(self, file_path):
        """导入学生数据"""
        try:
            # 读取文件
            df = self.read_file(file_path)
            
            # 清洗数据
            df = self.clean_data(df)
            
            # 必需字段验证
            required_columns = ['student_id', 'email', 'major']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                self.errors.append(f"Missing required columns: {', '.join(missing_columns)}")
                return False
            
            # 逐行导入
            for idx, row in df.iterrows():
                try:
                    # 使用学号作为username（忽略CSV中可能存在的username列）
                    username = str(row['student_id']).strip()
                    
                    # 检查学号是否已存在
                    existing_user = User.query.filter_by(username=username).first()
                    if existing_user:
                        self.errors.append(f"Row {idx+2}: Student ID '{username}' already exists")
                        self.fail_count += 1
                        continue
                    
                    # 创建用户（使用学号作为username）
                    user = User(
                        username=username,  # 使用学号作为username
                        email=str(row['email']).strip() if pd.notna(row.get('email')) else '',
                        role='student',
                        first_name=str(row.get('first_name', '')).strip() if pd.notna(row.get('first_name')) else '',
                        last_name=str(row.get('last_name', '')).strip() if pd.notna(row.get('last_name')) else '',
                        phone=str(row.get('phone', '')).strip() if pd.notna(row.get('phone')) else '',
                        college=str(row.get('college', '')).strip() if pd.notna(row.get('college')) else '',
                        department=str(row.get('department', '')).strip() if pd.notna(row.get('department')) else ''
                    )
                    user.set_password(row.get('password', 'password123'))
                    
                    db.session.add(user)
                    db.session.flush()  # 获取user.id
                    
                    # 处理入学日期
                    enrollment_date_val = None
                    if 'enrollment_date' in df.columns and pd.notna(row.get('enrollment_date')):
                        try:
                            enrollment_date_val = pd.to_datetime(row.get('enrollment_date')).date()
                        except:
                            enrollment_date_val = None
                    
                    # 创建学生profile
                    profile = StudentProfile(
                        user_id=user.id,
                        student_id=row['student_id'],
                        major=row['major'],
                        grade_level=int(row.get('grade_level')) if 'grade_level' in df.columns and pd.notna(row.get('grade_level')) else None,
                        enrollment_date=enrollment_date_val
                    )
                    
                    db.session.add(profile)
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"Row {idx+2}: {str(e)}")
                    self.fail_count += 1
                    db.session.rollback()
                    continue
            
            db.session.commit()
            return True
            
        except Exception as e:
            self.errors.append(f"File reading error: {str(e)}")
            db.session.rollback()
            return False
    
    def import_courses(self, file_path):
        """导入课程数据"""
        try:
            # 读取文件
            df = self.read_file(file_path)
            
            # 清洗数据
            df = self.clean_data(df)
            
            # 必需字段验证
            required_columns = ['code', 'name', 'credits']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                self.errors.append(f"Missing required columns: {', '.join(missing_columns)}")
                return False
            
            # 逐行导入
            for idx, row in df.iterrows():
                try:
                    # 检查课程是否已存在
                    existing_course = Course.query.filter_by(code=row['code']).first()
                    if existing_course:
                        self.errors.append(f"Row {idx+2}: Course code '{row['code']}' already exists")
                        self.fail_count += 1
                        continue
                    
                    # 查找教师
                    instructor_id = None
                    if 'instructor_id' in df.columns and pd.notna(row.get('instructor_id')):
                        instructor = User.query.filter_by(
                            username=row['instructor_id'],  # 现在使用工号
                            role='teacher'
                        ).first()
                        if instructor:
                            instructor_id = instructor.id
                    
                    # 创建课程
                    course = Course(
                        code=row['code'],
                        name=row['name'],
                        description=row.get('description', ''),
                        credits=float(row['credits']),
                        department=row.get('department', ''),
                        instructor_id=instructor_id,
                        semester=row.get('semester', ''),
                        academic_year=row.get('academic_year', ''),
                        course_category=row.get('course_category', ''),
                        class_number=row.get('class_number', ''),
                        max_students=int(row['max_students']) if 'max_students' in df.columns and pd.notna(row.get('max_students')) else None
                    )
                    
                    db.session.add(course)
                    db.session.flush()
                    
                    # 添加标签
                    if 'tags' in df.columns and pd.notna(row.get('tags')):
                        tags = str(row['tags']).split(',')
                        for tag_name in tags:
                            tag_name = tag_name.strip()
                            if tag_name:
                                tag = CourseTag(course_id=course.id, tag=tag_name)
                                db.session.add(tag)
                    
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"Row {idx+2}: {str(e)}")
                    self.fail_count += 1
                    db.session.rollback()
                    continue
            
            db.session.commit()
            return True
            
        except Exception as e:
            self.errors.append(f"File reading error: {str(e)}")
            db.session.rollback()
            return False
    
    def import_grades(self, file_path):
        """导入成绩数据"""
        try:
            # 读取文件
            df = self.read_file(file_path)
            
            # 清洗数据
            df = self.clean_data(df)
            
            # 必需字段验证
            required_columns = ['student_id', 'course_code', 'score']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                self.errors.append(f"Missing required columns: {', '.join(missing_columns)}")
                return False
            
            # 逐行导入
            for idx, row in df.iterrows():
                try:
                    # 查找学生
                    student_profile = StudentProfile.query.filter_by(
                        student_id=row['student_id']
                    ).first()
                    
                    if not student_profile:
                        self.errors.append(f"Row {idx+2}: Student ID '{row['student_id']}' not found")
                        self.fail_count += 1
                        continue
                    
                    # 查找课程
                    course = Course.query.filter_by(code=row['course_code']).first()
                    if not course:
                        self.errors.append(f"Row {idx+2}: Course code '{row['course_code']}' not found")
                        self.fail_count += 1
                        continue
                    
                    # 检查是否已有成绩记录
                    existing = StudentCourse.query.filter_by(
                        student_id=student_user.id,
                        course_id=course.id
                    ).first()
                    
                    if existing:
                        # 更新现有记录
                        existing.score = float(row['score'])
                        existing.midterm_score = float(row['midterm_score']) if 'midterm_score' in df.columns and pd.notna(row.get('midterm_score')) else None
                        existing.final_score = float(row['final_score']) if 'final_score' in df.columns and pd.notna(row.get('final_score')) else None
                        existing.status = row.get('status', 'completed')
                        existing.academic_year = row.get('academic_year', '')
                        existing.semester_term = row.get('semester_term', '')
                        existing.exam_type = row.get('exam_type', '正常考试')
                        existing.credit_type = row.get('credit_type', '主修')
                        existing.gpa = float(row['gpa']) if 'gpa' in df.columns and pd.notna(row.get('gpa')) else None
                        existing.class_number = row.get('class_number', '')
                        existing.graded_at = datetime.utcnow()
                    else:
                        # 创建新记录
                        # 处理选课日期
                        enroll_date_val = datetime.utcnow().date()
                        if 'enrollment_date' in df.columns and pd.notna(row.get('enrollment_date')):
                            try:
                                enroll_date_val = pd.to_datetime(row.get('enrollment_date')).date()
                            except:
                                pass
                        
                        student_course = StudentCourse(
                            student_id=student_user.id,
                            course_id=course.id,
                            enrollment_date=enroll_date_val,
                            academic_year=row.get('academic_year', ''),
                            semester_term=row.get('semester_term', ''),
                            exam_type=row.get('exam_type', '正常考试'),
                            credit_type=row.get('credit_type', '主修'),
                            gpa=float(row['gpa']) if 'gpa' in df.columns and pd.notna(row.get('gpa')) else None,
                            class_number=row.get('class_number', ''),
                            status=row.get('status', 'completed'),
                            score=float(row['score']),
                            midterm_score=float(row['midterm_score']) if 'midterm_score' in df.columns and pd.notna(row.get('midterm_score')) else None,
                            final_score=float(row['final_score']) if 'final_score' in df.columns and pd.notna(row.get('final_score')) else None,
                            total_classes=int(row['total_classes']) if 'total_classes' in df.columns and pd.notna(row.get('total_classes')) else None,
                            attended=int(row['attended']) if 'attended' in df.columns and pd.notna(row.get('attended')) else None,
                            attendance_rate=float(row['attendance_rate']) if 'attendance_rate' in df.columns and pd.notna(row.get('attendance_rate')) else None,
                            graded_at=datetime.utcnow()
                        )
                        
                        # 计算等级
                        score = float(row['score'])
                        if score >= 90:
                            student_course.letter_grade = 'A'
                        elif score >= 80:
                            student_course.letter_grade = 'B'
                        elif score >= 70:
                            student_course.letter_grade = 'C'
                        elif score >= 60:
                            student_course.letter_grade = 'D'
                        else:
                            student_course.letter_grade = 'F'
                        
                        db.session.add(student_course)
                    
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"Row {idx+2}: {str(e)}")
                    self.fail_count += 1
                    db.session.rollback()
                    continue
            
            db.session.commit()
            return True
            
        except Exception as e:
            self.errors.append(f"File reading error: {str(e)}")
            db.session.rollback()
            return False
    
    def import_student_grades(self, file_path):
        """合并导入学生基本信息与成绩数据
        期望列（至少包含以下关键列）：
        - 学生：student_id[, username, email, password, first_name, last_name, phone, college, department, major, grade_level, enrollment_date]
        - 成绩：course_code[, course_enrollment_date, academic_year, semester_term, exam_type, credit_type, gpa, status, score, midterm_score, final_score, total_classes, attended, attendance_rate, class_number]
        """
        try:
            # 读取文件
            df = self.read_file(file_path)

            # 清洗数据
            df = self.clean_data(df)

            # 必需字段验证
            required_columns = ['student_id', 'course_code', 'score']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                self.errors.append(f"Missing required columns: {', '.join(missing_columns)}")
                return False

            # 批次内缓存：避免同一学生在多行中重复创建档案
            processed_students = {}  # key: student_id, value: (user, profile)

            for idx, row in df.iterrows():
                try:
                    # 确保学生存在，不存在则创建
                    input_student_id = str(row['student_id']).strip()
                    username_value = str(row['username']).strip() if 'username' in df.columns and pd.notna(row.get('username')) else input_student_id

                    if input_student_id in processed_students:
                        student_user, student_profile = processed_students[input_student_id]
                    else:
                        with db.session.no_autoflush:
                            # 优先按学号查找已存在的profile
                            student_profile = StudentProfile.query.filter_by(student_id=input_student_id).first()
                            student_user = None

                            if student_profile:
                                student_user = User.query.get(student_profile.user_id)
                            else:
                                # 按用户名找用户
                                student_user = User.query.filter_by(username=username_value).first()
                                if not student_user:
                                    # 不存在则创建用户
                                    student_user = User(
                                        username=username_value,
                                        email=str(row['email']).strip() if pd.notna(row.get('email')) else None,
                                        role='student',
                                        first_name=str(row.get('first_name', '')).strip() if pd.notna(row.get('first_name')) else '',
                                        last_name=str(row.get('last_name', '')).strip() if pd.notna(row.get('last_name')) else '',
                                        phone=str(row.get('phone', '')).strip() if pd.notna(row.get('phone')) else '',
                                        college=str(row.get('college', '')).strip() if pd.notna(row.get('college')) else '',
                                        department=str(row.get('department', '')).strip() if pd.notna(row.get('department')) else ''
                                    )
                                    student_user.set_password(row.get('password', 'password123'))
                                    db.session.add(student_user)
                                    db.session.flush()

                                # 再次双重确认该用户是否已有profile（避免唯一键冲突）
                                existing_profile_by_user = StudentProfile.query.filter_by(user_id=student_user.id).first()
                                if existing_profile_by_user:
                                    student_profile = existing_profile_by_user
                                else:
                                    # 确认没有后再创建
                                    # 处理入学日期
                                    enrollment_date_val = None
                                    if 'enrollment_date' in df.columns and pd.notna(row.get('enrollment_date')):
                                        try:
                                            enrollment_date_val = pd.to_datetime(row.get('enrollment_date')).date()
                                        except:
                                            enrollment_date_val = None
                                    
                                    student_profile = StudentProfile(
                                        user_id=student_user.id,
                                        student_id=input_student_id,
                                        major=row.get('major', ''),
                                        grade_level=int(row.get('grade_level')) if 'grade_level' in df.columns and pd.notna(row.get('grade_level')) else None,
                                        enrollment_date=enrollment_date_val
                                    )
                                    db.session.add(student_profile)
                                    db.session.flush()

                        # 放入批次缓存，供后续相同学生复用
                        processed_students[input_student_id] = (student_user, student_profile)

                    # 查找课程
                    course = Course.query.filter_by(code=row['course_code']).first()
                    if not course:
                        self.errors.append(f"Row {idx+2}: Course code '{row['course_code']}' not found")
                        self.fail_count += 1
                        db.session.rollback()
                        continue

                    # 创建或更新成绩
                    existing = StudentCourse.query.filter_by(
                        student_id=student_user.id,
                        course_id=course.id
                    ).first()

                    enrollment_date_value = datetime.utcnow().date()
                    if 'course_enrollment_date' in df.columns and pd.notna(row.get('course_enrollment_date')):
                        try:
                            enrollment_date_value = pd.to_datetime(row.get('course_enrollment_date')).date()
                        except:
                            pass
                    elif 'enrollment_date' in df.columns and pd.notna(row.get('enrollment_date')):
                        try:
                            enrollment_date_value = pd.to_datetime(row.get('enrollment_date')).date()
                        except:
                            pass

                    if existing:
                        existing.enrollment_date = enrollment_date_value
                        existing.academic_year = row.get('academic_year', '')
                        existing.semester_term = row.get('semester_term', '')
                        existing.exam_type = row.get('exam_type', '正常考试')
                        existing.credit_type = row.get('credit_type', '主修')
                        existing.gpa = float(row['gpa']) if 'gpa' in df.columns and pd.notna(row.get('gpa')) else None
                        existing.class_number = row.get('class_number', '')
                        existing.status = row.get('status', 'completed')
                        existing.score = float(row['score']) if pd.notna(row.get('score')) else None
                        existing.midterm_score = float(row['midterm_score']) if 'midterm_score' in df.columns and pd.notna(row.get('midterm_score')) else None
                        existing.final_score = float(row['final_score']) if 'final_score' in df.columns and pd.notna(row.get('final_score')) else None
                        existing.total_classes = int(row['total_classes']) if 'total_classes' in df.columns and pd.notna(row.get('total_classes')) else None
                        existing.attended = int(row['attended']) if 'attended' in df.columns and pd.notna(row.get('attended')) else None
                        existing.attendance_rate = float(row['attendance_rate']) if 'attendance_rate' in df.columns and pd.notna(row.get('attendance_rate')) else None
                        existing.graded_at = datetime.utcnow()
                    else:
                        student_course = StudentCourse(
                            student_id=student_user.id,
                            course_id=course.id,
                            enrollment_date=enrollment_date_value,
                            academic_year=row.get('academic_year', ''),
                            semester_term=row.get('semester_term', ''),
                            exam_type=row.get('exam_type', '正常考试'),
                            credit_type=row.get('credit_type', '主修'),
                            gpa=float(row['gpa']) if 'gpa' in df.columns and pd.notna(row.get('gpa')) else None,
                            class_number=row.get('class_number', ''),
                            status=row.get('status', 'completed'),
                            score=float(row['score']) if pd.notna(row.get('score')) else None,
                            midterm_score=float(row['midterm_score']) if 'midterm_score' in df.columns and pd.notna(row.get('midterm_score')) else None,
                            final_score=float(row['final_score']) if 'final_score' in df.columns and pd.notna(row.get('final_score')) else None,
                            total_classes=int(row['total_classes']) if 'total_classes' in df.columns and pd.notna(row.get('total_classes')) else None,
                            attended=int(row['attended']) if 'attended' in df.columns and pd.notna(row.get('attended')) else None,
                            attendance_rate=float(row['attendance_rate']) if 'attendance_rate' in df.columns and pd.notna(row.get('attendance_rate')) else None,
                            graded_at=datetime.utcnow()
                        )
                        # 计算等级（按百分制）
                        if student_course.score is not None:
                            s = float(student_course.score)
                            if s >= 90:
                                student_course.letter_grade = 'A'
                            elif s >= 80:
                                student_course.letter_grade = 'B'
                            elif s >= 70:
                                student_course.letter_grade = 'C'
                            elif s >= 60:
                                student_course.letter_grade = 'D'
                            else:
                                student_course.letter_grade = 'F'
                        db.session.add(student_course)

                    self.success_count += 1

                except Exception as e:
                    import traceback
                    error_detail = traceback.format_exc()
                    self.errors.append(f"Row {idx+2}: {str(e)}\nTraceback: {error_detail}")
                    self.fail_count += 1
                    db.session.rollback()
                    continue

            db.session.commit()
            return True

        except Exception as e:
            self.errors.append(f"File reading error: {str(e)}")
            db.session.rollback()
            return False

    def get_summary(self):
        """获取导入摘要"""
        return {
            'success_count': self.success_count,
            'fail_count': self.fail_count,
            'errors': self.errors
        }

